﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for BranchesDAL
/// </summary>
public class BranchesDAL
{
	public BranchesDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    dbConnection db = new dbConnection();
    public  int CreateBranch(BranchesBO Branch)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

        dbConnection db = new dbConnection();
      
        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO [Branches] ( BranchName, AddressId,BrnchCrtdDt) VALUES (@BranchName, @AddressId,CONVERT(VARCHAR(20),@BrnchCrtdDt, 101))";
            cmd.Parameters.AddWithValue("@BranchName", Branch.BranchName);
            cmd.Parameters.AddWithValue("@AddressId", Branch.AddressId);
            cmd.Parameters.AddWithValue("@BrnchCrtdDt", Branch.BrnchCrtdDt);


            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }
    public  DataTable AddressFillDropDown()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet1 = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select AddressId,Addresses.AddressLine1+','+Addresses.AddressLine2 + ',' +Addresses.AddressLine3+','+Addresses.City+','+CAST( PhoneNumber AS nvarchar(50)) as Address from [Addresses]";
            dSet1 = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet1, "Addresses");

            return dSet1.Tables["Addresses"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet1.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }

    public  int UpdateBranch(BranchesBO Branch)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

        

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE [Branches] SET  [BranchName]=@BranchName, [AddressId]=@AddressId,[BrnchCrtdDt]=CONVERT(VARCHAR(20),@BrnchCrtdDt, 101) WHERE BranchId=@BranchId";
            cmd.Parameters.AddWithValue("@BranchId", Branch.BranchId);
            cmd.Parameters.AddWithValue("@BranchName", Branch.BranchName);
            cmd.Parameters.AddWithValue("@AddressId", Branch.AddressId);
            cmd.Parameters.AddWithValue("@BrnchCrtdDt", Branch.BrnchCrtdDt);


            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }

    public  int DeleteBranch(BranchesBO Branch)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;


        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM [Branches] WHERE BranchId=@BranchId";
            cmd.Parameters.AddWithValue("@BranchId", Branch.BranchId);

            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }
    public  DataTable Load()
    {

        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet1 = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select Branches.BranchId,Branches.BranchName,Branches.BrnchCrtdDt, Addresses.AddressLine1+','+Addresses.AddressLine2 + ',' +Addresses.AddressLine3+','+Addresses.City+','+CAST( PhoneNumber AS nvarchar(50)) as Address from Branches,Addresses where Branches.AddressId=Addresses.AddressId";
            dSet1 = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet1, "Branches");

            return dSet1.Tables["Branches"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet1.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }



  
}